﻿
Imports DTO
Public Class BoKichDienDAO

    Private ketnoi As ConnectDB

    Public Sub New()
        ketnoi = New ConnectDB()
    End Sub

    Public Function InsertBoKichDien(ByVal aq As BoKichDienDTO, ByVal flagIsUp As Integer)
        Dim MaBoKichDien As Integer = aq.MaBoKichDien
        Dim TenBoKichDien As String = aq.TenBoKichDien
        Dim MLoai As Integer = aq.MaLoai
        Dim HSX As String = aq.HangSanXuat
        Dim DAp As Single = aq.DienApTai
        Dim CongSuatTai As Single = aq.CongSuatTai
        Dim Gia As Integer = aq.Gia
        Dim sqlString As String
        If flagIsUp = 1 Then
            sqlString = "INSERT BoKichDien(TenBoKichDien,MaLoai,HangSanXuat,DienApTai,CongSuatTai,gia) VALUES(N'" + TenBoKichDien.ToString() + "'," + MLoai.ToString() + ",N'" + HSX.ToString() + "'," + DAp.ToString() + "," + CongSuatTai.ToString() + ", " + Gia.ToString() + ")"
        ElseIf flagIsUp = 2 Then
            sqlString = "UPDATE BoKichDien SET TenBoKichDien =N'" + TenBoKichDien.ToString() + _
                                                    "',MaLoai = " + MLoai.ToString() + _
                                                    ",HangSanXuat = N'" + HSX.ToString() + _
                                                    "', DienApTai = " + DAp.ToString() + _
                                                    ", CongSuatTai = " + CongSuatTai.ToString() + _
                                                    ", Gia = " + Gia.ToString() + " WHERE MaAcQuy = " + MaBoKichDien.ToString() + ""
        End If
        Return ketnoi.Execute(sqlString)
    End Function

    Public Function LoadComboListbox()
        Dim dt As DataTable
        dt = ketnoi.GetData("Select * From LoaiBoKichDien")
        Return dt
    End Function

    Public Function GetData()
        Dim MaBoKichDien As Integer
        Dim TenBoKichDien As String
        Dim MLoai As Integer
        Dim HSX As String
        Dim DAp As Single
        Dim CongSuatTai As Single
        Dim Gia As Integer
        Dim laq As BoKichDienDTO
        Dim laqList As New List(Of BoKichDienDTO)
        Dim dt As DataTable
        dt = ketnoi.GetData("Select * From BoKichDien")
        Dim i As Integer
        For i = 0 To dt.Rows.Count - 1
            MaBoKichDien = Val(dt.Rows(i)("MaBoKichDien"))
            TenBoKichDien = dt.Rows(i)("TenBoKichDien").ToString
            MLoai = Val(dt.Rows(i)("MaLoai"))
            HSX = dt.Rows(i)("HangSanXuat").ToString
            DAp = Val(dt.Rows(i)("DienApTai"))
            CongSuatTai = Val(dt.Rows(i)("CongSuatTai"))
            Gia = Val(dt.Rows(i)("Gia"))
            laq = New BoKichDienDTO(MaBoKichDien, TenBoKichDien, MLoai, HSX, DAp, CongSuatTai, Gia)
            laqList.Add(laq)
        Next
        Return laqList
    End Function

    Public Function TimKiem(ByVal aq As BoKichDienDTO)
        Dim sqlString As String
        Dim MaBoKichDien As Integer = aq.MaBoKichDien
        Dim TenBoKichDien As String = aq.TenBoKichDien
        Dim MaLoai As Integer = aq.MaLoai
        Dim HangSanXuat As String = aq.HangSanXuat
        Dim DienApTai As Single = aq.DienApTai
        Dim DienApTaiMin As Single = aq.DienApTaiMin
        Dim DienApTaiMax As Single = aq.DienApTaiMax
        Dim CongSuatTai As Single = aq.CongSuatTai
        Dim CongSuatTaiMin As Single = aq.CongSuatTaiMin
        Dim CongSuatTaiMax As Single = aq.CongSuatTaiMax
        Dim Gia As Single = aq.Gia
        Dim GiaMin As Integer = aq.GiaMin
        Dim GiaMax As Integer = aq.GiaMax

        If aq.MaBoKichDien <> 0 Then
            sqlString = "Select * From BoKichDien WHERE MaBoKichDien = " + aq.MaBoKichDien.ToString() + " "
        Else
            sqlString = "Select * From BoKichDien WHERE "
            Dim many As Boolean = False
            If TenBoKichDien <> "" Then
                sqlString += "TenBoKichDien Like '%" + TenBoKichDien.ToString() + "%' "
                If many = False Then
                    many = True
                End If
            End If
            If HangSanXuat <> "" Then
                If many = True Then
                    sqlString += " and "

                End If
                If many = False Then
                    many = True
                End If
                sqlString = sqlString + " HangSanXuat Like '%" + HangSanXuat.ToString() + "%' "
                If many = False Then
                    many = True
                End If
            End If
            If MaLoai <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "MaLoai Like '%" + MaLoai.ToString() + "%' "
                If many = False Then
                    many = True
                End If
            End If
            If DienApTaiMin <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "DienApTai >= '" + DienApTaiMin.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If DienApTaiMax <> 0 Then

                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "DienApTai <= '" + DienApTaiMax.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If DienApTaiMin <> 0 And DienApTaiMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "DienApTai >= '" + DienApTaiMin.ToString() + "' And DienApTai <= '" + DienApTaiMax.ToString() + "' "
                If many = False Then
                    many = True
                End If
            End If
            If CongSuatTaiMin <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "CongSuatTai >= '" + CongSuatTaiMin.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If CongSuatTaiMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "CongSuatTai <= '" + CongSuatTaiMax.ToString + "'"
                If many = False Then
                    many = True
                End If
            End If
            If CongSuatTaiMin <> 0 And CongSuatTaiMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "CongSuatTai <= '" + CongSuatTaiMax.ToString() + "' And DienAp >= '" + CongSuatTaiMin.ToString() + "' "
                If many = False Then
                    many = True
                End If
            End If
            If GiaMin <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Gia >= '" + GiaMin.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If GiaMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Gia <= '" + GiaMax.ToString() + "'"
                If many = False Then
                    many = True
                End If
            End If
            If GiaMin <> 0 And GiaMax <> 0 Then
                If many = True Then
                    sqlString += " and "
                End If
                sqlString = sqlString + "Gia <= '" + GiaMax.ToString() + "' And Gia >= '" + GiaMin.ToString() + "' "
                If many = False Then
                    many = True
                End If
            End If

        End If

        'sqlString = "Select * From ACQUY WHERE MaAcQuy Like '%" + MAcQuy + "%' or TenAcQuy Like '" + TAcQuy + "' or HangSanXuat Like '" + HSX + "' or MaLoai Like '" + MLoai + "' or '" + DAp + "' "
        Dim laq As BoKichDienDTO
        Dim aqList As New List(Of BoKichDienDTO)
        Dim dt As DataTable
        dt = ketnoi.GetData(sqlString)
        Dim i As Integer
        For i = 0 To dt.Rows.Count - 1
            MaBoKichDien = Val(dt.Rows(i)("MaBoKichDien"))
            TenBoKichDien = dt.Rows(i)("TenBoKichDien").ToString
            MaLoai = Val(dt.Rows(i)("MaLoai"))
            HangSanXuat = dt.Rows(i)("HangSanXuat").ToString
            DienApTai = Val(dt.Rows(i)("DienApTai"))
            CongSuatTai = Val(dt.Rows(i)("CongSuatTai"))
            Gia = Val(dt.Rows(i)("Gia"))
            laq = New BoKichDienDTO(MaBoKichDien, TenBoKichDien, MaLoai, HangSanXuat, DienApTai, CongSuatTai, Gia)
            aqList.Add(laq)
        Next
        Return aqList
    End Function

    Public Function DeleteAcQuy(ByVal id As Integer)
        Dim sqlstring As String
        sqlstring = "DELETE BoKichDien WHERE MaBoKichDien = " + id.ToString() + ""
        Return ketnoi.Execute(sqlstring)
    End Function

    Public Function SelectByID(ByVal id As Integer)
        Dim MaBoKichDien As Integer
        Dim TenBoKichDien As String
        Dim MLoai As Integer
        Dim HSX As String
        Dim DAp As Single
        Dim CongSuatTai As Single
        Dim Gia As Integer
        Dim laq As BoKichDienDTO
        Dim laqList As New List(Of BoKichDienDTO)
        Dim dt As DataTable
        dt = ketnoi.GetData("Select * From BoKichDien Where MaBoKichDien = " + id.ToString() + "")
        Dim i As Integer
        For i = 0 To dt.Rows.Count - 1
            MaBoKichDien = Val(dt.Rows(i)("MaBoKichDien"))
            TenBoKichDien = dt.Rows(i)("TenBoKichDien").ToString
            MLoai = Val(dt.Rows(i)("MaLoai"))
            HSX = dt.Rows(i)("HangSanXuat").ToString
            DAp = Val(dt.Rows(i)("DienApTai"))
            CongSuatTai = Val(dt.Rows(i)("CongSuatTai"))
            Gia = Val(dt.Rows(i)("Gia"))
            laq = New BoKichDienDTO(MaBoKichDien, TenBoKichDien, MLoai, HSX, DAp, CongSuatTai, Gia)
            laqList.Add(laq)
        Next
        Return laqList
    End Function

    Public Function SelectNameLoai(ByVal id As Integer)
        Dim MLoai As Integer
        Dim TLoai As String
        Dim laq As LoaiBoKichDienDTO
        Dim laqList As New List(Of LoaiBoKichDienDTO)
        Dim dt As DataTable
        dt = ketnoi.GetData("Select * From LoaiBoKichDien Where MaLoai = " + id.ToString() + "")
        Dim i As Integer
        For i = 0 To dt.Rows.Count - 1
            MLoai = Val(dt.Rows(i)("MaLoai"))
            TLoai = dt.Rows(i)("TenLoai").ToString()
            laq = New LoaiBoKichDienDTO(MLoai, TLoai)
            laqList.Add(laq)
        Next
        Return laqList
    End Function
End Class
